
* this do-file finds all port-to-port connections passing the canal
* previously named 14_PanamaRoutes

clear all

*cd "/Users/inga/Dropbox/Ais project/inga"

global main "/Users/inga/Dropbox/Ais project/Repository"
cd "$main/2_fastest_routes"

global data "$main/data_intermediate"
global source "$main/data_input"



* Indirect routes: make indicator whether trip start is on atlantic or pacific side

use "$data/ais_container_cleaned.dta", clear
g pan = 1 if country=="PA"
egen pa=total(pan), by(ship_id_new)

keep if pa>0
order pan

bysort ship_id: g start=1 if pan==1 & pan[_n-1]==.
bysort ship_id: g ptrip=sum(start) 
replace pan = ptrip if pan!=.
drop start ptrip

g panan= 1 if substr(port_name,1,11)=="PANAMA ANCH"

egen pAT = total(panan) if port_name=="PANAMA ANCH ATLANTIC", by(pan ship_id)
egen pPA = total(panan) if port_name=="PANAMA ANCH PACIFIC", by(pan ship_id)

egen pATL=mean(pAT), by(ship_id pan)
egen pPAC=mean(pPA), by(ship_id pan)
order pATL pPAC

g keepme=1 if pATL!=. | pPAC!=. 

drop if pan==. & inTransit=="true"

bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="A"
bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="A"
bysort ship_id: replace keepme = 2 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="P" & move_type=="departure"

bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="A"
bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="A"
bysort ship_id: replace keepme = 20 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="P" & move_type=="arrival"

*drop anchorages in between origin and panama
drop if keepme==1 & pan==.

bysort ship_id: replace pan=pan[_n+1] if keepme==2
bysort ship_id: replace pan=pan[_n-1] if keepme==20

*keep only trips that involve one actual stop
g realP = 1 if keepme==1 & port_type=="P" & move_type=="arrival" & pan!=. & inTransit=="false"
egen RealP = total(realP), by(ship_id pan)

replace keepme = . if RealP==0
drop if keepme==.

drop RealP

bysort ship_id pan: replace realP=sum(realP) if realP!=.
replace keepme=3 if realP==1
bysort ship_id pan: g atlantic = 1 if keepme==2 & port_name[_n+1] == "PANAMA ANCH ATLANTIC"
bysort ship_id pan: g pacific = 1 if keepme==2 & port_name[_n+1] == "PANAMA ANCH PACIFIC"

savesome using "$data/incomingPanama" if keepme==2 | keepme==3, replace


* find last actual stop within Panama
egen LastRealP = max(realP), by(ship_id pan)

replace keepme=4 if realP==LastRealP
drop atlan pac

bysort ship_id pan: g atlantic = 1 if keepme==20 & port_name[_n-1] == "PANAMA ANCH ATLANTIC"
bysort ship_id pan: g pacific = 1 if keepme==20 & port_name[_n-1] == "PANAMA ANCH PACIFIC"


keep if keepme==4 | keepme==20

keep pan port_name port_id atla pac keepme ship_id country


reshape wide port_name port_id atla pac country, i(ship_id pan) j(keepme)
drop if port_id4==. | port_id20==.
count if atlantic20==. & pacific20==.

collapse (firstnm) atlantic20 pacific20, by(port* countr*)
count if atlantic20==. & pacific20==.


*br if atlantic20==. & pacific20==.

replace atlantic=1 if port_name4=="BALBOA" & port_name20=="ROTTERDAM MAASVLAKTE" & atlantic20==. & pacific20==.
replace pacific=1 if port_name4=="MANZANILLO" & port_name20=="VALPARAISO" & atlantic20==. & pacific20==.


rename *4 D_*
rename *20 A_*
save "$data/outgoingPanama", replace

u "$data/incomingPanama", clear
keep pan port_name port_id atla pac keepme ship_id country

reshape wide port_name port_id atla pac country, i(ship_id pan) j(keepme)

drop if port_id2==. | port_id3==.
count if atlantic2==. & pacific2==.
collapse (firstnm) atlantic2 pacific2, by(port* countr*)
count if atlantic2==. & pacific2==.
*br if atlantic2==. & pacific2==.
replace pacific=1 if port_name2=="YOKOHAMA" & port_name3=="BALBOA" & atlantic2==. & pacific2==.

rename *2 D_*
rename *3 A_*

append using "$data/outgoingPanama"

g ASoP="E" if A_port_name=="MANZANILLO" | A_port_name=="COLON 2000"| A_port_name=="COLON" | A_port_name=="CRISTOBAL"
replace ASoP="W" if A_port_name=="BALBOA" | A_port_name=="RODMAN" 
g DSoP="E" if D_port_name=="MANZANILLO" | D_port_name=="COLON 2000"| D_port_name=="COLON" | D_port_name=="CRISTOBAL"
replace DSoP="W" if D_port_name=="BALBOA" | D_port_name=="RODMAN" 


*fix errors by hand

list if A_atla==1 & A_paci==1
replace A_pacif=. if A_port_name=="ANTWERP" & D_port_name=="CRISTOBAL"
replace A_pacif=. if A_port_name=="ALGECIRAS" & D_port_name=="MANZANILLO"
replace A_atlan=. if A_port_name=="CALLAO" & D_port_name=="MANZANILLO"
replace A_pacif=. if A_port_name=="ANTWERP" & D_port_name=="BALBOA"
replace A_atlan=. if A_port_name=="XIAMEN" & D_port_name=="COLON"
replace A_atlan=. if A_port_name=="QINGDAO" & D_port_name=="COLON"


g XPanama =1 if ASoP=="E" & D_pacific==1
replace XPanama =1 if ASoP=="W" & D_atlantic==1
replace XPanama =1 if DSoP=="W" & A_atlantic==1
replace XPanama =1 if DSoP=="E" & A_pacific==1

duplicates drop A_port_name A_country D_port_name D_country A_port_id D_port_id, force

keep if XPanama==1


keep XPan A_port_name A_country D_port_name D_country A_port_id D_port_id

save "$data/XPanama_det", replace



*** find non-stop routes that cross

use "$data/ais_container_cleaned.dta", clear

g pan = 1 if country=="PA"
egen pa=total(pan), by(ship_id_new)


keep if pa>0
order pan

bysort ship_id: g start=1 if pan==1 & pan[_n-1]==.
bysort ship_id: g ptrip=sum(start) 
replace pan = ptrip if pan!=.
drop start ptrip

g panan= 1 if substr(port_name,1,11)=="PANAMA ANCH"
egen pAT = total(panan) if port_name=="PANAMA ANCH ATLANTIC", by(pan ship_id)
egen pPA = total(panan) if port_name=="PANAMA ANCH PACIFIC", by(pan ship_id)
egen pANY = total(panan), by(pan ship_id)
order pAT pPA pANY
count if pan==1 & pANY==0
*** only one trip involving any port in Panama does not go through one of these anchorages


egen pATL=mean(pAT), by(ship_id pan)
egen pPAC=mean(pPA), by(ship_id pan)
order pATL pPAC


g pananyP= 1 if port_type=="P" & pan!=.
egen pPT = mean(pananyP), by(ship_id pan)
g keepme=1 if pATL!=. & pPAC!=. & pPT!=1

drop if pan==. & inTransit=="true"

bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="A"
bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="A"
bysort ship_id: replace keepme = 3 if keepme==. & (keepme[_n+1]==1 | keepme[_n+2]==1) & port_type=="P" & move_type=="departure"

bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="A"
bysort ship_id: replace keepme = 1 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="A"
bysort ship_id: replace keepme = 4 if keepme==. & (keepme[_n-1]==1 | keepme[_n-2]==1) & port_type=="P" & move_type=="arrival"

drop if keepme==.
order keepme
drop if keepme==1 & pan==.
bysort ship_id: replace pan=pan[_n+1] if keepme==3
bysort ship_id: replace pan=pan[_n-1] if keepme==4

bysort ship_id pan: g PAmonth = month(dofc(date)) if keepme[_n-1]==3
bysort ship_id pan: g PAday = day(dofc(date)) if keepme[_n-1]==3

egen Pmonth=mean(PAmonth), by(pan ship_id)
egen Pday=mean(PAday), by(pan ship_id)

keep if keepme==3 | keepme==4

keep keepme pan ship_id date port_name port_id country Pm Pd

sort ship_id pan

reshape wide date port_name port_id country, i(pan ship_id) j(keepme)
drop if date3==. | date4==.

rename *4 A_*
rename *3 D_*

g XPanama=1

collapse (firstnm) XPanama, by(A* D*)

duplicates drop A_port_name A_country D_port_name D_country A_port_id D_port_id, force

keep XPan A_port_name A_country D_port_name D_country A_port_id D_port_id

append using "$data/XPanama_det"


*make intra-trans-Panama observations
preserve
keep A* XP
keep if A_c=="PA"
duplicates drop
save help, replace
drop X
rename A* D*
cross using help
drop if A_port_name==D_port_name

replace XP=. if (A_port_name=="MANZANILLO" | A_port_name=="COLON 2000"| A_port_name=="COLON" | A_port_name=="CRISTOBAL") & (D_port_name=="MANZANILLO" | D_port_name=="COLON 2000"| D_port_name=="COLON" | D_port_name=="CRISTOBAL")
replace XP=. if (A_port_name=="BALBOA" | A_port_name=="RODMAN" ) & (D_port_name=="BALBOA" | D_port_name=="RODMAN" )
save help, replace
restore

append using help
erase help.dta

duplicates drop A_port_id D_port_id, force

save "$data/XPanama_det", replace

u "$data/XPanama_det", clear
drop *name
merge m:1 A_port_id using "$data/port_cluster_name", keepusing(A_port_name)
drop if _merge==2
drop _merge

rename A_* A2_*
rename D_port_id A_port_id

merge m:1 A_port_id using "$data/port_cluster_name", keepusing(A_port_name)
drop if _merge==2
drop _merge
rename A_port_name D_port_name
rename A_port_id D_port_id
rename A2_* A_*
drop *id
duplicates drop


save "$data/XPanama", replace






u "$data/XPanama", clear

merge m:1 A_port_name A_country using "$data/port_id_list_balanced_small"
keep if _merge==3
drop _merge
rename A_* AA_*
rename D_* A_*
merge m:1 A_port_name A_country using "$data/port_id_list_balanced_small"
keep if _merge==3
drop _merge
rename A_* D_* 
rename AA_* A_*
drop *country *name
drop if A_acid == D_acid

save "$data/XPanama_ids_small", replace



erase "$data/XPanama_det.dta"
erase "$data/XPanama.dta"
erase "$data/outgoingPanama.dta"
erase "$data/incomingPanama.dta"


